#!/usr/bin/perl

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_Items.txt';
$print_outs = $baseurl . '/prints_Items.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST QUERY A SET OF ITEM RECORDS

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#   COPY #  ICODE1  ICODE2  I TYPE  BARCODE OUT DATE    DUE DATE    CALL #(ITEM)    VOLUME  LCHKIN  COURSE ID   SAVE ITEM  
#   ODUE DATE   RECAL DATE  TOT CHKOUT  TOT RENEW   LOCATION    LOANRULE    STATUS  INTL USE    LABEL LOC   NOTE(ITEM)  
#   RECORD #(ITEM)  CREATED(ITEM)   UPDATED(ITEM)   RECORD #(BIBLIO)    CALL #(BIBLIO)

# NOTE: the LOCATION is the ITEM LOCATION

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./items_all/ OR ./items_updated/

if ($ARGV[0] eq 'CREATING') {
$next_item_record = 1000000;
}

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

$folder_name = 'items';

$where = $baseurl . '/items';

while (defined($next_file = <$where/*.out>)) {

print "filename: " . $next_file . "\n";

($batch_textfiles = $next_file) =~ s/items/items_textfiles/g;

$batch_loader = IO::File->new("> $batch_textfiles") || die "cannot open $batch_textfiles for writing: $!\n";
flock($batch_loader, 2);

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

        while(<IN>) {

            if($_ !~ /^COPY #/) {
                @fields = split(/\t/);

                %query_insert = (
                    copy => '',
                    icode1 => '',
                    item_status => '',
                    itype => '',
                    barcode => '',
                    outdate => '',
                    duedate => '',
                    item_callnumber => '',
                    volume => '',
                    lchkin => '',
                    course_id => '',
                    save_item => '',
                    oduedate => '',
                    recaldate => '',
                    tot_chkout => '',
                    tot_renew => '',
                    item_iloc => '',
                    loanrule => '',
                    circ_status => '',
                    intl_use => '',
                    labelloc => '',
                    notes => '',
                    item_record => '',
                    record_owner => $institution,
                    item_sequence => '0', 
                    item_created => '',
                    item_updated => '',
                    pub_year => "0000",
                    local_control_number => '',
                    universal_identifier => ''
                );

##START: CREATE DELETED ITEM RECORDS

if ($ARGV[0] eq 'CREATING') {

$this_item_record = substr($fields[22], 1, 7);

        while($next_item_record < $this_item_record) {

            $query_insert{'item_record'} = $institution . 'i' . $next_item_record;
            $query_insert{'item_status'} = 'x';

            @keys = keys %query_insert;
            @values = values %query_insert;

            $mysql_keys = join(", ", @keys);
            $mysql_values = join("\t", @values);
            $mysql_values =~ s/\\\t/\\ \t/g;

            print $batch_loader $mysql_values . "\n";
        
            $next_item_record++;
        }

        $next_item_record++;

}

##END: CREATE DELETED ITEM RECORDS

###START: CREATE ITEM RECORDS

		$query_insert{'copy'} = $fields[0];
		$query_insert{'icode1'} = $fields[1];
		$query_insert{'item_status'} = $fields[2];
		$query_insert{'itype'} = $fields[3];
		$query_insert{'barcode'} = $fields[4];
		$query_insert{'outdate'} = $fields[5];
		$query_insert{'duedate'} = $fields[6];
		($query_insert{'item_callnumber'} = $fields[7]) =~ s/^[\s]*//;
		$query_insert{'volume'} = $fields[8];
		$query_insert{'lchkin'} = $fields[9];
		$query_insert{'course_id'} = $fields[10];
		$query_insert{'save_item'} = $fields[11];
		$query_insert{'oduedate'} = $fields[12];
		$query_insert{'recaldate'} = $fields[13];
		$query_insert{'tot_chkout'} = $fields[14];
		$query_insert{'tot_renew'} = $fields[15];
		$query_insert{'item_iloc'} = $institution . '\t' . $fields[16];
		$query_insert{'loanrule'} = $fields[17];
		$query_insert{'circ_status'} = $fields[18];
		$query_insert{'intl_use'} = $fields[19];
		$query_insert{'labelloc'} = $fields[20];
		$query_insert{'notes'} = $fields[21];
		$query_insert{'item_record'} = $institution . 'i' . substr($fields[22], 1, 7);
		$query_insert{'item_created'} = $fields[23];
		$query_insert{'item_updated'} = $fields[24];

                foreach $date_field( outdate, duedate, lchkin, oduedate, recaldate, item_created, item_updated ) {
                    if($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{4})/) {
                        $query_insert{$date_field} = $3 . "-" . $1 . "-" . $2;
                    } elsif($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{2})/) {
                        $query_insert{$date_field} = "19" . $3 . "-" . $1 . "-" . $2;
                    }
                }

                if($query_insert{'volume'} =~ /([1][7-9]{1}[0-9]{2})/ || $query_insert{'volume'} =~ /([2][0][0-9]{2})/ || $query_insert{'volume'} =~ /^([1][7-9]{1}[0-9]{2})/ || $query_insert{'volume'} =~ /^([2][0][0-9]{2})/) {
                $query_insert{'pub_year'} = $1;
                }

                @local_control_numbers = ();
                @callnumbers = ();
                
                $diff = $#fields - 25;

                $limit = $diff/2;

                for($a = 0; $a <= $limit; $a++) {
                    $b = 25 + $a;
                    $c = $b + 1 + $limit;
                    
                    push(@local_control_numbers, $institution . substr($fields[$b], 0, 8));

                    $bib_callnumber = $fields[$c];
                    chomp($bib_callnumber);
                    $bib_callnumber =~ s/^[\s]*//;
                    if($query_insert{'item_callnumber'} eq '') {
                        push(@callnumbers, $bib_callnumber);
                    } else {
                        push(@callnumbers, $query_insert{'item_callnumber'});
                    }
                }

###END: CREATE ITEM RECORDS

####START: PRINT VALUES TO BATCH FILES

@keys = keys %query_insert;
@values = values %query_insert;

    for($a = 0; $a < $limit; $a++) {

$query_insert{'local_control_number'} = $local_control_numbers[$a];
$query_insert{'item_callnumber'} = $callnumbers[$a];

$mysql_keys = join(", ", @keys);
@values = values %query_insert;

$mysql_values = join("\t", @values);
$mysql_values =~ s/\\\t/\\ \t/g;

print $batch_loader $mysql_values . "\n";

####END: PRINT VALUES TO BATCH FILES

}

                undef($mysql_values);
                undef(@keys);
                undef(@values);
                undef(%query_insert);

        }
    
    }

    close(IN) || "cannot close $next_file: $!";

unlink($next_file);

    $batch_loader->autoflush(1);
    close($batch_loader, 2) || "cannot close $batch_loader: $!";

}

$items_keys_textfiles = $baseurl . '/' . $folder_name . '_textfiles/items.key';

$items_keys_loader = IO::File->new("> $items_keys_textfiles") || die "cannot open $items_keys_textfiles for writing: $!\n";
binmode( $items_keys_loader, ':utf8' );
flock($items_keys_loader, 2);

    print $items_keys_loader $mysql_keys . "\n";

        $items_keys_loader->autoflush(1);
        close($items_keys_loader, 2) || "cannot close $items_keys_loader: $!";


($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_of_file_creation_time = "$HOUR:$MIN:$SEC";

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

@current_time = localtime;

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

print "last_update_time: " . $last_update_time . "\n";


if ($ARGV[0] eq 'CREATING') {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'items_record_table'}");
    $this_table = $db_local_tables_hash{'items_record_table'};
} else {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'items_record_temp_table'}");
    $this_table = $db_local_tables_hash{'items_record_temp_table'};
}

$where = $baseurl . '/' . $folder_name . '_textfiles';

#####START: INITIAL TABLE LOAD

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " DISABLE KEYS");
#}

while (defined($next_file = <$where/*.key>)) {

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

            while(<IN>) {
                $mysql_keys = $_;
                chomp($mysql_keys);
            }
    close(IN) || "cannot close $next_file: $!";

}

while (defined($next_file = <$where/*.out>)) {

    $sth2 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $this_table FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "intial load died in local\n";

unlink($next_file);

}

unlink($items_keys_textfiles);

    if($xmlopac eq 'yes') {
        $sequence_script = $baseurl . "/items_item_sequences_load.pl $ARGV[0] $ARGV[1]";
        system($sequence_script);
    }

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " ENABLE KEYS");
#}


#####END: INITIAL TABLE LOAD

######START: UPDATE ACTUAL ITEM TABLE WITH DATA IN TEMP TABLE

if ($ARGV[0] ne 'CREATING') {

$this_table = $db_local_tables_hash{'items_record_table'};
$that_table = $db_local_tables_hash{'items_record_temp_table'};

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@keys, $row[0]); ## field name
    }

$query->finish();

pop(@keys);

$mysql_keys = join(", ", @keys);

@update_strings = ();

    foreach $field_name (@keys) {
        push(@update_strings, $this_table . '.' . $field_name . ' = ' . $that_table . '.' . $field_name);
    }

    $temp_updates = join(", ", @update_strings);

        $querystring = "UPDATE $this_table, $that_table
            SET $temp_updates
            WHERE $this_table.item_record = $that_table.item_record
            AND $this_table.local_control_number = $that_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "DELETE $that_table.*
            FROM $that_table
            LEFT JOIN $this_table
            ON $that_table.item_record = $this_table.item_record
            WHERE $that_table.item_record = $this_table.item_record
            AND $that_table.local_control_number = $this_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "INSERT $this_table
            ($mysql_keys) 
            SELECT $mysql_keys from $that_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();
            
}

######END: UPDATE ACTUAL ITEM TABLE WITH DATA IN TEMP TABLE

#######START: UPDATE $that_table TABLE WITH universal_identifiers

$this_table = $db_local_tables_hash{'bibs_record_table'};
$that_table = $db_local_tables_hash{'items_record_table'};

$update_string = "UPDATE $that_table, $this_table
set $that_table.universal_identifier = $this_table.universal_identifier
WHERE $that_table.solr_update >= ?
AND $that_table.local_control_number = $this_table.local_control_number";

print $update_string . "\n" . $last_update_time . "\n";


$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($last_update_time)
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();

#######END: UPDATE $that_table TABLE WITH universal_identifiers


########START: UPDATE $that_table TABLE

if ($ARGV[0] ne 'CREATING') {

$this_table = $db_local_tables_hash{'items_record_table'};
$that_table = $db_local_tables_hash{'bibs_record_table'};

$update_string = "UPDATE $that_table, $this_table
set $that_table.solr_update = NOW()
WHERE $this_table.solr_update >= ?
AND $that_table.local_control_number = $this_table.local_control_number";

$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($last_update_time)
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();

}

########END: UPDATE $that_table TABLE

#########START: CREATING FILES FOR LOAD INTO CONSORTIUM ITEMS AND BIBS TABLES

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

foreach $table_name (item, bib) {

$this_table = $db_local_tables_hash{$table_name . 's_record_table'};

$recordcount = 0;

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name
    }

$query->finish();

pop(@fields_array);

$fields_array_string = join(", ", @fields_array);

$consortium_keys_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium' . $table_name . 's.key';

$consortium_keys_loader = IO::File->new("> $consortium_keys_textfiles") || die "cannot open $consortium_keys_textfiles for writing: $!\n";
binmode( $consortium_keys_loader, ':utf8' );
flock($consortium_keys_loader, 2);

    print $consortium_keys_loader $fields_array_string . "\n";

        $consortium_keys_loader->autoflush(1);
        close($consortium_keys_loader, 2) || "cannot close $consortium_keys_loader: $!";

if ($ARGV[0] eq 'CREATING') {
    $IN_string = "'n', 's', 'z', 'x'";
} else {
    $IN_string = "'n', 's', 'z'";
}

$limit_start = 0;
$rownumber = 1;
$status = $table_name . '_status';
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE solr_update >= ?
            AND $status NOT IN ($IN_string)
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

        if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
        }

$consortium_batch_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium_from_2' . $folder_name . '_' . $table_name . $recordcount .  '.txt';

$consortium_batch_loader = IO::File->new("> $consortium_batch_textfiles") || die "cannot open $consortium_batch_textfiles for writing: $!\n";
binmode( $consortium_batch_loader, ':utf8' );
flock($consortium_batch_loader, 2);


    }

$recordcount++;

pop(@query_array);

foreach(@query_array) {
    s/\t/\\t/g;
}

    $query_array_string = join("\t", @query_array);
    $query_array_string =~ s/\\\t/\\ \t/g;
    $query_array_string =~ s/\n/\\n/g;

    print $consortium_batch_loader $query_array_string . "\n";
    $line_count++;
    }

}

$query->finish();

if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
}

undef(@fields_array);

}

#########END: CREATING FILES FOR LOAD INTO CONSORTIUM ITEMS AND BIBS TABLES

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nEnd time: " . $end_time . "\n";


#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}

